<%@ page language="java" import="java.sql.*"%>
<%@ page language="java" import="java.util.ArrayList"%>
<%@ page language="java" import="db.Config"%>
<%@ page language="java" import="org.json.simple.*"%>

<%-- -------- Open Connection Code -------- --%>
<%
	Connection conn = null;
	PreparedStatement pstmt = null;
	ResultSet rs = null;

	String role = (String) session.getAttribute("role");
	if (role == null)
		role = "user";

	try {
		// Load JDBC Driver class file
		Class.forName(Config.jdbcDriver);

		// Open a connection to the database using DriverManager
		conn = DriverManager.getConnection(Config.connectionURL,
				Config.username, Config.password);

		String action = request.getParameter("action");

		if (action != null && action.equals("insert")) {
			JSONObject result = new JSONObject();
			// Begin transaction
			conn.setAutoCommit(false);

			// Create the prepared statement and use it to
			// INSERT product values INTO the product table.
			pstmt = conn
					.prepareStatement("INSERT INTO product (name, sku, category, price) VALUES (?, ?, ?, ?)");

			pstmt.setString(1, request.getParameter("name"));
			pstmt.setString(2, request.getParameter("sku"));
			pstmt.setInt(3,
					Integer.parseInt(request.getParameter("category")));
			float price;
			try {
				price = Float.parseFloat(request.getParameter("price"));
			} catch (NumberFormatException ex) {
				price = 0.0f;
			}
			pstmt.setFloat(4, price);
			int rowCount = 0;
			boolean safeFlag = true;
			try {
				rowCount = pstmt.executeUpdate();

				// Commit transaction
				conn.commit();
				conn.setAutoCommit(true);
			} catch (Exception e) {
				safeFlag = false;
				//out.println(e.getMessage());
				//out.println("in exception");
				result.put("Response", "Failed to insert!");
				result.put("result", "fail");
			}
			if (safeFlag) {
				pstmt = conn
						.prepareStatement("SELECT ID FROM product WHERE name = ? AND sku = ? AND category = ?");
				pstmt.setString(1, request.getParameter("name"));
				pstmt.setString(2, request.getParameter("sku"));
				pstmt.setInt(3, Integer.parseInt(request
						.getParameter("category")));
				ResultSet getIdRs = pstmt.executeQuery();
				int insertPid = -1;
				if (getIdRs != null && getIdRs.next()) {
					insertPid = getIdRs.getInt("ID");
				}
				//out.println("got id:"+insertPid);
				if (insertPid > 0) {
					result.put("Response", "Successfully inserted!");
					result.put("result", "succ");
					result.put("ID", "" + insertPid);
				} else {
					result.put("Response", "Failed to insert!");
					result.put("result", "fail");
				}
			}
			out.print(result);
			out.flush();
		}

		if (action != null && action.equals("update")) {

			conn.setAutoCommit(false);

			// Create the prepared statement and use it to
			// UPDATE product values in the product table.
			pstmt = conn
					.prepareStatement("UPDATE product SET name = ?, sku = ?, category = ?, price = ? WHERE id = ?");

			pstmt.setString(1, request.getParameter("name"));
			pstmt.setString(2, request.getParameter("sku"));
			pstmt.setInt(3,
					Integer.parseInt(request.getParameter("category")));
			float price;
			try {
				price = Float.parseFloat(request.getParameter("price"));
			} catch (NumberFormatException ex) {
				price = 0.0f;
			}
			pstmt.setFloat(4, price);
			pstmt.setInt(5,
					Integer.parseInt(request.getParameter("id")));
			int rowCount = pstmt.executeUpdate();

			// Commit transaction
			conn.commit();
			conn.setAutoCommit(true);

			JSONObject result = new JSONObject();

			result.put("Response", "Successfully updated!");

			out.print(result);
			out.flush();

		}

		if (action != null && action.equals("delete")) {

			// Begin transaction
			conn.setAutoCommit(false);

			// Create the prepared statement and use it to
			// DELETE product FROM the product table.
			pstmt = conn
					.prepareStatement("DELETE FROM product WHERE id = ?");

			pstmt.setInt(1,
					Integer.parseInt(request.getParameter("id")));
			int rowCount = pstmt.executeUpdate();

			// Commit transaction
			conn.commit();
			conn.setAutoCommit(true);
			JSONObject result = new JSONObject();

			result.put("Response", "Successfully deleted!");

			out.print(result);
			out.flush();
		}
%>
<%
	ArrayList<String> idList = new ArrayList<String>();
		ArrayList<String> nameList = new ArrayList<String>();
		pstmt = conn.prepareStatement("SELECT id, name FROM category");
		rs = pstmt.executeQuery();
		while (rs.next()) {
			idList.add(rs.getString("id"));
			nameList.add(rs.getString("name"));
		}
%>


<%
	if (action != null && action.equals("search")) {
			String searchCategory = request
					.getParameter("searchCategory");
			String keyword = request.getParameter("name");
			if (keyword != null) {
				keyword = keyword.toLowerCase();
				if (searchCategory == null
						|| searchCategory.equals("all")) {
					pstmt = conn
							.prepareStatement("SELECT * FROM product WHERE LOWER(name) LIKE ?");
					pstmt.setString(1, "%" + keyword + "%");
				} else {
					pstmt = conn
							.prepareStatement("SELECT * FROM product WHERE category = ? AND LOWER(name) LIKE ?");
					pstmt.setInt(1, Integer.parseInt(searchCategory));
					pstmt.setString(2, "%" + keyword + "%");
				}
			} else
				pstmt = conn.prepareStatement("SELECT * FROM product");
		} else {
			String browseCategory = request
					.getParameter("browseCategory");
			if (browseCategory == null || browseCategory.equals("all"))
				pstmt = conn.prepareStatement("SELECT * FROM product");
			else {
				pstmt = conn
						.prepareStatement("SELECT * FROM product WHERE category = ?");
				pstmt.setInt(1, Integer.parseInt(browseCategory));
			}
		}

		rs = pstmt.executeQuery();
		while (rs.next()) {
			int id = rs.getInt("id");
			String name = rs.getString("name");
			String sku = rs.getString("sku");
			String category = rs.getString("category");
			String price = rs.getString("price");
%>

<%
	}
%>


<%-- -------- Close Connection Code -------- --%>
<%
	// Close the ResultSet
		rs.close();

		// Close the Statement
		pstmt.close();

		// Close the Connection
		conn.close();
	} catch (SQLException e) {

		// Wrap the SQL exception in a runtime exception to propagate
		// it upwards
		//throw new RuntimeException(e);
		String errorMsg = e.getMessage();
		//TODO: Parse error message to display user-friendly messages.
%>

<%
	} finally {
		// Release resources in a finally block in reverse-order of
		// their creation

		if (rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {
			} // Ignore
			rs = null;
		}
		if (pstmt != null) {
			try {
				pstmt.close();
			} catch (SQLException e) {
			} // Ignore
			pstmt = null;
		}
		if (conn != null) {
			try {
				conn.close();
			} catch (SQLException e) {
			} // Ignore
			conn = null;
		}
	}
%>
